package model;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Queue;

import javax.sql.rowset.FilteredRowSet;

import com.sun.rowset.FilteredRowSetImpl;

public class PatientSearch 
{	
	//hashmap linking keys possibly sent in to the sql clause they will translate to in the query
	final static HashMap<String,String> conditions = new HashMap<String,String>();
	static
	{
		conditions.put("firstName", "p.FirstName LIKE ?");
		conditions.put("lastName", "p.LastName LIKE ?");
		conditions.put("race", "p.Race LIKE ?");
		conditions.put("gender", "p.Gender = ?");
		conditions.put("insurance", "p.Insurer LIKE ?");
		conditions.put("minAge", "p.DateOfBirth <= ?");
		conditions.put("maxAge", "p.DateOfBirth >= ?");
		conditions.put("bloodType", "p.BloodType = ?");
		conditions.put("religion", "p.Religion LIKE ?");
		conditions.put("country", "p.Country LIKE ?");
		conditions.put("state", "p.StateProvince LIKE ?");
		conditions.put("city", "p.City LIKE ?");
		conditions.put("postalCode", "p.PostalCode = ?");
		conditions.put("dateLastVisitMin", "v.DateOfVisit >= ?");
		conditions.put("dateLastVisitMax", "v.DateOfVisit <= ?");
		conditions.put("reason", "v.ReasonForVisit LIKE ?");
		conditions.put("drLastName", "d.LastName LIKE ?");
	};
	
//	 	search function accomplishes the major searching of this patient search
//	 	3 parameters:
//			HashMap<String,String> criteriaIN - Holds limited number of data key pairs:
//				Always contains:
//					"medicationsCombinationType" (search for patients taking Med1 AND Med2 
//					"allergiesCombinationType"	   or else taking either Med1 OR Med2)
//				May contain (with non empty string value):
//					firstName
//					lastName
//					race
//					gender
//					insurance
//					minAge
//					maxAge
//					bloodType
//					religion
//					country
//					state
//					city
//					postalCode
//					dateLastVisitMin
//					dateLastVisitMax
//					reason
//					drLastName
//			List<String> allergiesIN
//				Each item in the list is a single allergy to search on, whether 
//					search is AND or OR determined by a variable in the criteria hasmap
//			List<String> medicationsIN
//				Each item in the list is a single medication to search on, whether 
//					search is AND or OR determined by a variable in the criteria hasmap
//	Written by:  Christopher Speirs
	public List<Patient> search(HashMap<String,String> criteriaIN, List<String> allergiesIN, List<String> medicationsIN) {
		//create copies of original searching parameters (empty strings show up when passed from servlet, objects act immutable, the following 4 parameters solve that issue)
		HashMap<String,String> criteria = new HashMap<String,String>(criteriaIN);
		HashMap<String,String> visits  = new HashMap<String,String>();
		List<String> allergies  = new ArrayList<String>(allergiesIN);
		List<String> medications = new ArrayList<String>(medicationsIN);
		
		//the following 2 variables store the data sent in criteriaIN under the keys "medicationsCombinationType" 
		//and "allergiesCombinationType".  After assigning these variables those keys removed from the hashmap
		//in order to have only search columns remain in the table
		String medCombin;
		String allerCombin;

		//moves key value pairs associated with visits including the doctor of a visit to the visits hashtable
		if (criteria.containsKey("dateLastVisitMin")) visits.put("dateLastVisitMin", criteria.remove("dateLastVisitMin"));
		if (criteria.containsKey("dateLastVisitMax")) visits.put("dateLastVisitMax", criteria.remove("dateLastVisitMax"));
		if (criteria.containsKey("reason")) visits.put("reason", criteria.remove("reason"));
		if (criteria.containsKey("drLastName")) visits.put("drLastName", criteria.remove("drLastName"));
		
		//test stuff that should be implemented or removed eventually
		if (!criteria.containsKey("medicationsCombinationType")) criteria.put("medicationsCombinationType", "add");
		if (!criteria.containsKey("allergiesCombinationType")) criteria.put("allergiesCombinationType", "add");
		// end test stuff that....
		
		medCombin = criteria.remove("medicationsCombinationType");
		allerCombin = criteria.remove("allergiesCombinationType");
		if (medCombin.equals("add")) medCombin = "AND";
		else medCombin = "OR";
		if (allerCombin.equals("add")) allerCombin = "AND";
		else allerCombin = "OR"; 

		allergies.remove("");
		medications.remove("");
		//At this point all 4 search parameter objects contain only items we want to search on.  This division
		//makes the following steps simple.

		//Variables to be used hereafter
		List<Patient> patientFiles = new LinkedList<Patient>(); //the object to return at the end of this method
		Queue<Object> params = new LinkedList<Object>(); //a queue of parameters that need to be added in order 
		FilteredRowSet frs = null;	//the data object
		Connection conn = null;
		String sqlquery = "";
		
		//This if block generates any inner query which would narrow down the search to specific
		//PatientID's if and only if at least one allergy was passed in the allergiesIN list
		if (allergies.size() > 0) {
			//if the search on allergies was "Any"
			if (allerCombin.equals("OR")) {
				sqlquery += "PatientID IN ( SELECT DISTINCT PatientID FROM PatientAllergies AS pa, Allergies AS a " +
						"WHERE pa.AllergyID = a.AllergyID AND ( a.AllergyName LIKE ? ";
				params.add("%" + allergies.get(0) + "%");
				for (int i = 1; i < allergies.size(); i++) {
					sqlquery += " OR a.AllergyName LIKE ? ";
					params.add("%" + allergies.get(i) + "%");
				}
				sqlquery += ") ) ";
			}
			//if the search on allergies was "All"
			else {
				int i;
				for ( i = 0; i < allergies.size(); i++) {
					if (i > 0) sqlquery += " AND ";
					sqlquery += "PatientID IN ( SELECT PatientID FROM PatientAllergies AS pa, Allergies AS a " +
							"WHERE pa.AllergyID = a.AllergyID AND a.AllergyName LIKE ? ";
					params.add("%" + allergies.get(i) + "%");
				}
				for ( int j = i ; j > 0; j--) sqlquery += " ) ";
			}
		}
		
		//This if block generates any inner query which would narrow down the search to specific
		//PatientID's if and only if at least one medication was passed in the medicationsIN list
		if (medications.size() > 0) {
			if (!sqlquery.equals("")) sqlquery += "AND ";
			//if the search on medication was "ANY"
			if (medCombin.equals("OR")) {
				sqlquery += "PatientID IN ( SELECT DISTINCT PatientID FROM PatientMeds AS pm, Medications AS m " +
						"WHERE pm.MedicationID = m.MedicationID AND ( m.Name LIKE ? ";
				params.add("%" + medications.get(0) + "%");
				for (int i = 1; i < medications.size(); i++) {
					sqlquery += " OR m.Name LIKE ? ";
					params.add("%" + medications.get(i) + "%");
				}
				sqlquery += ") ) ";
			}
			//if the search on medication was "ALL"
			else {
				int i;
				for ( i = 0; i < medications.size(); i++) {
					if (i > 0) sqlquery += " AND ";
					sqlquery += "PatientID IN ( SELECT PatientID FROM PatientMeds AS pm, Medications AS m " +
							"WHERE pm.MedicationID = m.MedicationID AND m.Name LIKE ? ";
					params.add("%" + medications.get(i) + "%");
				}
				for ( int j = i ; j > 0; j--) sqlquery += " ) ";
			}
		}
		
		//This if block generates any inner query which would narrow down the search to specific
		//PatientID's if and only if at least one visits data was searched by.  This is the data
		//at this point stored in the visits hashmap
		if (visits.size() > 0) {
			if (!sqlquery.equals("")) sqlquery += "AND ";
			sqlquery += "PatientID IN ( SELECT DISTINCT PatientID FROM Visits AS v, Doctors AS d WHERE v.DoctorID = d.DoctorID ";
			String[] keys = visits.keySet().toArray(new String[0]);
			for (int i = 0; i < keys.length; i++) {
				sqlquery += " AND " + conditions.get(keys[i]);
			}
			sqlquery += " ) ";
		}
		
		//This if block generates the last portions of the WHERE clause for the outer query. All
		//parameters searched on in the criteria hashmap are added to the sqlquery due to this block
		if (criteria.size() > 0) {
			if (!sqlquery.equals("")) sqlquery += "AND ";
			sqlquery += getCriteriaPortion(criteria, params);
		}

		//if and only if the sqlquery is not an empty string do we need to have a WHERE clause in the final
		//query
		if (!sqlquery.equals("")) sqlquery = "WHERE " + sqlquery;
		
		//finally the select and from portions of the outer query are added to the string and complete the query
		//At this point we have exactly the query we need to search according to the users input
		sqlquery = "SELECT PatientID, FirstName, LastName, MiddleInitial, Race, Gender, Insurer, DateOfBirth, " +
				"BloodType, Religion, Country, StateProvince AS 'State/Province', City, PostalCode, Address FROM Patients AS p " +
				sqlquery;
		
		try
		{
			conn = this.getSqlserverConnection();
			frs = new FilteredRowSetImpl();  // use of frs found at http://docs.oracle.com/javase/tutorial/jdbc/basics/filteredrowset.html
			//System.err.println(sqlquery); //extra debugging
			frs.setCommand(sqlquery);
			setParams(frs, criteria, visits, params); //sets params to the frs object thus guarding from sql injection
			frs.execute(conn);
			
			//this while loop puts all the data returned into the patient objects and stores them in the list of
			//patients to be returned from this method
			while(frs.next())
			{ 
				patientFiles.add(new Patient(frs.getString("FirstName"), frs.getString("MiddleInitial"),
						frs.getString("LastName"), frs.getString("Gender"), frs.getString("BloodType"),  
						frs.getString("DateOfBirth"), frs.getString("Address"), frs.getString("City"), frs.getString("State/Province"),
						frs.getString("PostalCode"), frs.getString("PatientID"), frs.getString("Country")));
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				if (frs != null) frs.close();
				if (conn != null) conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return patientFiles;
	}

	private String getCriteriaPortion(HashMap<String,String> criteria, Queue<Object> params) {
		String temp = "";
		String[] keys = criteria.keySet().toArray(new String[0]);
		if (keys.length > 0) {
			int i = 0;
			temp += conditions.get(keys[i++]);
			while(i < keys.length) {
				temp += " AND " + conditions.get(keys[i++]);
			}
		}
		return temp;
	}

	private void setParams(FilteredRowSet frs, HashMap<String,String> criteria, HashMap<String,String> visits, Queue<Object> params) throws SQLException {
		int i = 1;
		while (params.size() > 0) frs.setString(i++, params.poll().toString());

		String[] keys = visits.keySet().toArray(new String[0]);
		for ( String k : keys) {
			switch (k) {
			case "dateLastVisitMin":
			case "dateLastVisitMax":
				frs.setDate(i++, Date.valueOf(visits.get(k)));
				break;
			case "reason":
			case "drLastName":
				frs.setString(i++, "%" + visits.get(k) + "%");
			}
		}
		
		keys = criteria.keySet().toArray(new String[0]);
		for (String k : keys) {
			switch (k) {
			case "maxAge":
			case "minAge":
			case "dateLastVisitMin":
			case "dateLastVisitMax":
				frs.setDate(i++, Date.valueOf(criteria.get(k)));
				break;
			case "firstName":
			case "lastName":
			case "race":
			case "insurance":
			case "medicare":
			case "religion":
			case "country":
			case "state":
			case "city":
			case "reason":
			case "drLastName":
				frs.setString(i++, "%" + criteria.get(k) + "%");
				break;
			case "gender":
			case "postalCode":
			case "bloodType":
				frs.setString(i++, criteria.get(k));
				break;
			default:
			}
		}
	}
	
	
	//**************************getPatientDetail Method****************************************//
	//Method is used to get data on a particular patient, which is defined by the passed patient id parameter
	//Returns: a patient object with the specified patient's information initialized in the patient object
	public Patient getPatientDetail(int id) 
	{
		Connection conn = null;
		FilteredRowSet frs = null;
		Patient p = null;
		
		try 
		{
			//string to create SQL query to request patient information from the Patients table in the database
			String sqlquery = "SELECT PatientID AS PatientID, FirstName AS FirstName, LastName AS LastName, MiddleInitial AS MiddleInitial, Race AS Race, " +
					"Gender AS Gender, Insurer AS Insurer, DateOfBirth AS DOB, BloodType AS BloodType, Religion AS Religion, Country AS Country," +
					" StateProvince AS State, City AS City, PostalCode AS PostalCode, Address AS Address FROM Patients WHERE " +
					"PatientID= ?";
			
			conn = this.getSqlserverConnection(); //creates a connection to the database
			
			frs = new FilteredRowSetImpl();  // use of frs found at http://docs.oracle.com/javase/tutorial/jdbc/basics/filteredrowset.html
			frs.setCommand(sqlquery); 	//sets the command to the defined sqlquery string
			frs.setInt(1, id); 	//replaces the ? with the the id variable's value
			frs.execute(conn); 	//executes the sql query
			
			//loops through the data in the filtered rowset
			while(frs.next())
			{ 
				//creates a new patient object and uses the Patient constructor to initialize its field with data retrieved from the database
				p = new Patient(frs.getString("PatientID"), frs.getString("FirstName"),
						frs.getString("LastName"), frs.getString("MiddleInitial"), frs.getString("Race"), frs.getString("Gender"), 
						frs.getString("Insurer"), frs.getString("DOB"), frs.getString("BloodType"), frs.getString("Religion"),
						frs.getString("Country"), frs.getString("State"), frs.getString("City"), frs.getString("PostalCode"), 
						frs.getString("Address"));
			}
			
		} catch (Exception e) 
		{
			e.printStackTrace();
		}
		finally 
		{

			try 
			{
				frs.close();
			} catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		return p; //returns the patient object
	}
	
	
	//**************************getMedicines Method****************************************//
	//Method is used to get medication data on a particular patient, which is defined by the passed patient id parameter
	//Returns: List of list of strings is returned with the specified patient's mediacation names and dosages
	public List<List<String>> getMedicines(int id) 
	{
		Connection conn = null;
		FilteredRowSet frs = null;
		List<List<String>> medsRows = new ArrayList<List<String>>(); 
		try 
		{
			conn = this.getSqlserverConnection();	//creates a connection to the database
			String sqlquery;
			
			//string to create SQL query to request a patient's medication information from the database
			sqlquery = "Select m.Name, pm.Dosage FROM Medications AS m, PatientMeds AS pm " +
					"WHERE m.MedicationID = pm.MedicationID AND pm.PatientID = ?";
			frs = new FilteredRowSetImpl();
			frs.setCommand(sqlquery); 	//sets the command to the defined sqlquery string
			frs.setInt(1, id); 	//replaces the ? with the the id variable's value
			frs.execute(conn); 	//executes the sql query
			
			//loops through the data in the filtered rowset
			while(frs.next())
			{ 
				List<String> medsCol = new ArrayList<String>(); //list to hold name and dosage for each medication
				medsCol.add(frs.getString("Name"));		//adds medication name to medsCol List
				medsCol.add(frs.getString("Dosage"));	//adds medication dosage to medsCol List
				medsRows.add(medsCol);	//add the medsCol list to a List of List of Strings
			}
			
		} catch (Exception e) 
		{
			e.printStackTrace();
		}
		finally 
		{

			try 
			{
				frs.close();
			} catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		return medsRows; //returns the list of list of medication strings
	}
	
	
	//**************************getAllergies Method****************************************//
	//Method is used to get allergy data on a particular patient, which is defined by the passed patient id parameter
	//Returns: List of strings is returned with the specified patient's allergy names
	public List<String> getAllergies(int id) 
	{
		Connection conn = null;
		FilteredRowSet frs = null;
		List<String> allergies = new ArrayList<String>(); 
		try {
			conn = this.getSqlserverConnection(); //creates a connection to the database
			String sqlquery;
			
			//string to create SQL query to request a patient's allergy information from the database
			sqlquery = "SELECT a.AllergyName FROM Allergies AS a, PatientAllergies AS p " +
					"WHERE a.AllergyID = p.AllergyID AND p.PatientID= ?";
			
			frs = new FilteredRowSetImpl();
			frs.setCommand(sqlquery); 	//sets the command to the defined sqlquery string
			frs.setInt(1, id); 	//replaces the ? with the the id variable's value
			frs.execute(conn); 	//executes the sql query
			
			//loops through the data in the filtered rowset
			while(frs.next())
			{ 
				//adds allergy name to allergies List
				allergies.add(frs.getString("AllergyName"));
			}
			
		} catch (Exception e) 
		{
			e.printStackTrace();
		}
		finally 
		{

			try 
			{
				frs.close();
			} catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		return allergies;  //returns the list of allergies
	}
	
	
	//**************************getVisits Method****************************************//
	//Method is used to get visit data on a particular patient, which is defined by the passed patient id parameter
	//Returns: List of list of strings is returned with the specified patient's visit information, such as: 
	//visit date, reason for visit, doctor, and doctor's specialization
	public List<List<String>> getVisits(int id) 
	{
		Connection conn = null;
		FilteredRowSet frs = null;
		List <List<String>> visitsRows = new ArrayList<List<String>>();  
		try {
			conn = this.getSqlserverConnection();
			String sqlquery;
			
			//string to create SQL query to request a patient's visit information from the database
			sqlquery = "SELECT v.DateOfVisit, v.ReasonForVisit, d.LastName, d.Specialization " +
					"FROM Visits AS v, Doctors AS d " +
					"WHERE v.DoctorID = d.DoctorID AND v.PatientID = ?";
			
			frs = new FilteredRowSetImpl();
			frs.setCommand(sqlquery); 	//sets the command to the defined sqlquery string
			frs.setInt(1, id); 	//replaces the ? with the the id variable's value
			frs.execute(conn); 	//executes the sql query
			
			//loops through the data in the filtered rowset
			while(frs.next())
			{ 		
				List<String> visitsCol = new ArrayList<String>();  //list to hold strings containing visit information
				visitsCol.add(frs.getString("DateOfVisit"));	//adds visit date to visitsCol List
				visitsCol.add(frs.getString("ReasonForVisit"));	//adds visit reason to visitsCol List
				visitsCol.add("Dr. " + frs.getString("LastName"));	//adds doctor name to visitsCol List
				visitsCol.add(frs.getString("Specialization"));	//adds doctor specialization to visitsCol List
				
				visitsRows.add(visitsCol); //add the visitsCol list to a List of List of Strings
			}
			
		} catch (Exception e) 
		{
			e.printStackTrace();
		}
		finally 
		{

			try 
			{
				frs.close();
			} catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		return visitsRows; //return the list of list of string visit data
	}
	
	
	private Connection getSqlserverConnection() throws Exception {
		String connStr = "jdbc:sqlserver://rl01s27mwd.database.windows.net:1433;database=patientsearch;user=cs3750@rl01s27mwd;password={9)zV{Ql>};";
		DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
		Connection conn = DriverManager.getConnection(connStr);
		
		return conn;
	}
	
//	public static void main(String[] args) throws SQLException {
//		PatientSearch ps = new PatientSearch();
//		List<Patient> list;
//		HashMap<String,String> criteria = new HashMap<String, String>();
//		List<String> allergies = new ArrayList<String>();
//		List<String> medications = new ArrayList<String>();
//		
//		criteria.put("postalCode", "94131");
////		criteria.put("firstName", "Justin");
////		criteria.put("lastName", "Spiers");
////		criteria.put("address", "East");
////		criteria.put("gender", "M");
//
////		criteria.put("minAge", "1800-01-21");
////		criteria.put("maxAge", "1917-01-21");
////		criteria.put("minAge", "1918-01-21");
////		criteria.put("minAge", "2023-01-21");
//		
////		allergies.add("Shellfish");
////		allergies.add("Penicillin");
//
////		medications.add("Penicillin");
////		medications.add("Epinephrine");
//		
//		list = ps.search(criteria, allergies, medications);
//		printLIST(list);
//	}

//	private static void printLIST(List<Patient> list) {
//		for (int i = 0; i < list.size(); i++) {
//			System.out.println(
//					list.get(i).getPatientID() + " " +
//					list.get(i).getName() + " " +
//					list.get(i).getGender() + " " +
//					list.get(i).getRace() + " " +
//					list.get(i).getBloodType() + " " +
//					list.get(i).getDOB() + " " +
//					list.get(i).getAddress() + " " +
//					list.get(i).getCity() + " " +
//					list.get(i).getState() + " " +
//					list.get(i).getPostalCode() + " " +
//					list.get(i).getCountry() + " " +
//					list.get(i).getInsurer() + " " +
//					list.get(i).getReligion()
//					);
//		}
//	}
}